<?php
/**
 * 《电话管理》实现类
 *
 * @author 孙晓晔
 * @version $Id: Cdr.class.php 1035 2010-03-26 05:24:59Z sunxy $
 */
require_once(dirname(__FILE__) . '/TelUtil.class.php');

class Cdr extends TelUtil {
	public $tbl = Constant::tbl_cdr;

	public $cdr = array(
		'-' => '请选择',
		'ns_sip' => '南山',
		'sj_sip' => '沙井',
		'fy_sip' => '福永',
		'qx_sip' => '清溪',
		'dh_sip' => '短号',
	);

	public $category_dst = array(
		'-' => '请选择',
		'1' => '内部分机',

		'2' => '本地固话',
		'3' => '本地手机',

		'4' => '长途电话',
		'5' => '网络电话',
		'6' => '移动短号',
			
		'7' => '深圳电信',
		'8' => '东莞移动',
			
		'9' => '打入电话',
	);

	public $disposition = array(
		'-' => '请选择',
		'ANSWERED' => '已接',
		'NO ANSWER' => '未接',
		'BUSY' => '占线',
		'FAILED' => '失败',		
	);

	public $groupby = array(
		'-' => '请选择',
		'src' => '主叫',
		'dst' => '被叫',
		'billsec' => '时长',
		'cost' => '费用',
			
		'1' => '小时',
		'2' => '日期',
		'3' => '月份',
	);

	public $orderby = array(
		'-' => '请选择',
		'calldate' => '时间',
		'duration' => '时长',
		'src' => '来源地',
		'dst' => '目的地',
	);

	function __construct() {
		$request['update'] = '$Date: 2009/02/16 07:36:07 $';
		$request['revision'] = '$Revision: 1.1 $';

		parent::__construct($request);
	}

	function pretreat($controller) {
		parent::pretreat($controller);

		$controller->assign_by_ref('cdr_option', $this->cdr);
		$controller->assign_by_ref('dst_option', $this->category_dst);
		$controller->assign_by_ref('disposition_option', $this->disposition);
	}

	function tab($controller, $request) {		
		$data = array(
			'电话记录' => array('cc'=>'tel.php?do=Cdr.find', 'js'=>'1'),	
				
			'拔打日志' => array('cc'=>'tel.php?do=Call.find', 'js'=>'1'),
			'正在通话' => array('cc'=>'tel.php?do=Asterisk.find', 'js'=>''),
		
			'分机信息' => array('cc'=>'emp.php?do=Exten.find', 'js'=>'1'),
			'IP分配' => array('cc'=>'emp.php?do=Mac.find', 'js'=>'1'),
		
			'黑名单' => array('cc'=>'tel.php?do=Black.find', 'js'=>'1'),
			'电话费率' => array('cc'=>'tel.php?do=Rate.find', 'js'=>''),
			'手机号码' => array('cc'=>'tel.php?do=Mobile.find', 'js'=>'1'),
		
			'拔号规则' => array('cc'=>'tel.php?do=Rule.find', 'js'=>''),
			'电话详单' => array('cc'=>'tel.php?do=CdrCt.find', 'js'=>'1'),
		
			'统计分析' => array('cc'=>'tel.php?do=Cdr.tj_tab', 'js'=>''),
		);
		
		$ret = $this->_tab($data);

		$controller->assign_by_ref('result', $ret);

		$controller->display($request, 'tab');
		return true;
	}
	
	function tj_tab($controller, $request) {		
		$data = array(
			'电话记录' => array('cc'=>'tel.php?do=Cdr.group', 'js'=>''),	
			'电话详单' => array('cc'=>'tel.php?do=CdrCt.group', 'js'=>'1'),
		);
		
		$ret = $this->_tab($data);

		$controller->assign_by_ref('result', $ret);

		$controller->display($request, 'tj_tab');
		return true;
	}
	
	/**
	 *
	 *
	 * @param Controller $controller
	 * @param array $request
	 */
	function find($controller, $request) {
		$this->pretreat($controller);
			
		$controller->assign('t1', strtotime('-7 day'));
		// $controller->assign('t2', strtotime('+1 day'));

		$controller->assign_by_ref('cdr', $this->_host());

		$controller->display($request, 'tel/cdr/find');
		return true;
	}

	function _host() {
		require_once APP_LIB . 'IpAddr.class.php';
		$ipaddr = new IpAddr();

		$ip = $_SERVER["SERVER_ADDR"];

		if ($ipaddr->contains('192.168.0.0/21', $ip)) {
			return 'sj_sip';
		}

		if ($ipaddr->contains('192.168.16.0/22', $ip)) {
			return 'ns_sip';
		}
		
		if ($ipaddr->contains('192.168.8.0/22', $ip)) {
			return 'qx_sip';
		}

		return '-';
	}

	function _orderby_x($controller, $row, $px) {
		$orderby = $this->orderby['1'];
		$row['orderby_x'] = $row['createtime'];

		if (empty($px) || $px == '-' || $px == '1') {
			$controller->assign('orderby', $orderby);

			return $row;
		}

		if ($px == '2') {
			$orderby = $this->orderby[$px];
			$row['orderby_x'] = $row['modifytime'];
		}

		if ($px == '`join`') {
			$orderby = $this->orderby[$px];
			$row['orderby_x'] = $row['join'];
		}

		$controller->assign('orderby', $orderby);

		return $row;
	}

	function orderby_dd($px) {
		if ($px == '1') {
			return "calldate DESC";
		}

		return $px;
	}

	function _where($request) {
		$my = $_SESSION['emp'];
		$src = $my['exten'];

		$src_s = $request['src_s'];
		$dst_s = $request['dst_s'];
		$disposition_s = $request['disposition_s'];

		$billsec1_s = $request['billsec1_s'];
		$billsec2_s = $request['billsec2_s'];

		$dept_s = $this->aaaa($request["dept_s"]);

		if (isset($request['my'])) {
			$where = " where src = '" . $src . "'";
		} else {
			$where = " WHERE 1=1";
		}

		if (!empty($dept_s)) {
			$where .= " AND src " . $dept_s;
		}

		if (!empty($src_s)) {
			$where .= " AND src like '" . $src_s . "%'";
		}

		if (!empty($dst_s)) {
			$where .= " AND dst like '" . $dst_s . "%'";
		}

		$category = $this->category($request['category_dst_s']);
		if (!empty($category)) {
			$where .= " AND " . $category;
		}

		if (!empty($disposition_s) && $disposition_s <> '-') {
			$where .= " AND disposition = '" . $disposition_s . "'";
		}

		if (!empty($billsec1_s)) {
			$billsec1_s *= 60;
			$where .= " AND billsec > '" . $billsec1_s . "'";
		}

		if (!empty($billsec2_s)) {
			$billsec2_s *= 60;
			$where .= " AND billsec < '" . $billsec2_s . "'";
		}

		$where .= $this->_where_default($request);
		$where = $this->_where_aa($where);

		return $where;
	}

	function _where_default($request) {
		
		$id_s = $request['id_s'];
		$t1_s = $request['t1_s'];
		$t2_s = $request['t2_s'];
		
		$dd_s = $request['dd_s'];
		
		if (empty($t1_s) && empty($dd_s)) {
			$t1_s = date('Y-m-d', strtotime('-7 day'));
		}

		$where = '';
		
		if (!empty($id_s)) {
			$where .= " AND a.id = '" . $id_s . "'";
		}
		
		if (!empty($t1_s)) {
			$where .= " AND calldate > '" . $t1_s . "'";
		}

		if (!empty($t2_s)) {
			$where .= " AND calldate < '" . $t2_s . "'";
		}
		
		if (!empty($dd_s)) {
			$where .= " AND DATE_FORMAT(calldate, '%Y-%m-%d') = '" . $dd_s . "'";
		}

		return $where;
	}
	
	function _sip($cdr) {
		
		if ($cdr == 'qx_sip') {
			require_once(dirname(__FILE__) . '/CdrDg.class.php');
			$ret = new CdrDg();
			
			return $ret;
		}
		
		return $this;
	}

	/**
	 *
	 *
	 * @param Controller $controller
	 * @param array $request
	 */
	function findPost($controller, $request) {
		$cdr = $request['cdr_s'];
		if (empty($cdr) || $cdr == '-') {
			$cdr = 'cdr';
		}

		$aa = $this->_sip($cdr);
		
		return $aa->_findPost($controller, $request);
	}
	
	function _findPost($controller, $request) {
		$this->pretreat($controller);
		
		$this->findPara($controller, $request);
		
		$cdr = $request['cdr_s'];
		if (empty($cdr) || $cdr == '-') {
			$cdr = 'cdr';
		}
		
		$db = newdb($cdr);
		$fmt = "SELECT * FROM %s a %s %s";
		$where = $this->_where($request);
		$orderby = $this->_orderby("calldate");
		$sql = sprintf($fmt, $this->tbl, $where, $orderby);
		debug($sql);
		
		$pager_option = array (
			'db' => $db,
			'sql' => $sql,
			'PageSize' => $this->pagesize,
			'CurrentPageID' => $request['p'],
			'numItems' => $request['n']
		);
		
		$pager = @new Pager($pager_option);
		$data = $pager->getPageData();
		
		$this->rate = 0;
		if ($pager->number > 0) {
			$count = 0;
			$from = $pager->from;
			while ($count < $pager->number) {
				$row = $data[$count];
				
				$row = $this->_view($row['uniqueid'], $cdr);
		
				$data[$count] = $row;
				$count++;
			}
			$controller->assign_by_ref('result', $data);
			$controller->assign_by_ref('pager', $pager);
		}
		
		if ($this->_is_admin()) {
			$controller->display($request, 'tel/cdr/sunxy/list');
		} else {
			$controller->display($request, 'tel/cdr/list');
		}
		
		return true;
	}

	function category($category) {
		if (empty($category) || $category == '-') {
			return '';
		}

		if ($category == '1') {
			return "dst REGEXP '^(2|6|8)[0-9]{3}$'";
		}

		if ($category == '2') {
			return "dst REGEXP '^(9|195|196)[0-9]{8}$'";
		}

		if ($category == '3') {
			return "dst REGEXP '^(9|195|196)1(3|4|5|7|8)[0-9]{9}$'";
		}

		if ($category == '4') {
			return "dst REGEXP '^(9|195|196)(0|17909|11808|96688)[0-9]{10,}$'";
		}

		if ($category == '5') {
			return "dst REGEXP '^1(7|91|92)[0-9]{9,}$'";
		}
		
		if ($category == '6') {
			return "dst REGEXP '^(1906|6|1966006|96006)(666|[0-9]{4,5})$'";
		}
		
		if ($category == '7') {
			return "dst REGEXP '^(194|195)[0-9]{5,}$'";
		}
		
		if ($category == '8') {
			return "dst REGEXP '^(196|197)[0-9]{5,}$'";
		}
		
		if ($category == '9') {
			return "channel REGEXP '^(DAHDI|Zap|Local)'";
		}
	}

	function _file($src, $dst) {
		$list = array('6595', '8595', '6268', '8268', '8768', '8035');
		if (in_array($src, $list)) {
			return true;
		}

		if (in_array($dst, $list)) {
			return true;
		}

		return false;
	}

	function _admin() {
		if ($this->_is_admin()) {
			return true;
		}

		return false;
	}

	function xls($controller, $request) {
		$this->pretreat($controller);

		$this->findPara($controller, $request);

		$request['tpl'] = "xls";

		$my = $_SESSION['emp'];
		$src = $my['exten'];

		$cdr = $request['cdr_s'];
		if (empty($cdr) || $cdr == '-') {
			$cdr = 'cdr';
		}

		$db = newdb($cdr);
		$fmt = "SELECT * FROM %s a %s %s";
		$where = $this->_where($request);
		$orderby = $this->_orderby("calldate");
		$sql = sprintf($fmt, $this->tbl, $where, $orderby);
		debug($sql);

		$result = $db->query($sql);

		$total = 0;
		while ($row = $db->fetch_array($result)) {
			if (preg_match("/^(2|6|8)\d{3}$/i", $row['dst'])) {
				continue;
			}

			$row['src_o'] = $this->_number($row['src']);
			$row['dst_o'] = $this->_number($row['dst']);

			$total += $row['billsec'];

			$row['billsec_t'] = $this->printtime($row['billsec']);
			$minute = ceil($row['billsec'] / 60);

			$row['rate'] = $row['dst_o']['rate'];
			if (preg_match('/^9/', $row['dst']) && $row['rate'] == 0.22) {
				if ($minute < 2) {
					$row['fee'] = 0.22;
				} else {
					$row['fee'] = 0.22 + ($minute - 3) * 0.11;
				}
			} else {
				if ($row['billsec'] > 10) {
					$row['fee'] = $row['rate'] * $minute;
				}
			}

			$fee += $row['fee'];

			$data[] = $row;
		}
		$controller->assign_by_ref('result', $data);

		$total_t = $this->printtime($total);
		$controller->assign_by_ref('total_t', $total_t);
		$controller->assign_by_ref('fee', $fee);
		
		$filename = sprintf("%s.xls", date("YmdHis"));
		header('Content-Disposition:application/vnd.ms-excel; filename=' . $filename);

		$controller->display($request, 'tel/cdr/list');

		return true;
	}
	
	/**
	 *
	 *
	 * @param Controller $controller
	 * @param array $request
	 */
	function view($controller, $request) {
		$uniqueid = $request['id'];

		$cdr = $request['cdr_s'];
		if (empty($cdr) || $cdr == '-') {
			$cdr = 'cdr';
		}
		
		$row = $this->_view($uniqueid, $cdr);

		$controller->assign_by_ref('result', $row);

		if ($this->_is_admin()) {
			$controller->display($request, 'tel/cdr/sunxy/view');
		} else {
			$controller->display($request, 'tel/cdr/view');
		}
		
		return true;
	}
	
	function _view($uniqueid, $cdr) {
		$db = newdb($cdr);

		$fmt = "SELECT * FROM %s WHERE uniqueid = '%s'";
		$sql = sprintf($fmt, $this->tbl, $uniqueid);
		$result = $db->query($sql);
		$row = $db->fetch_array($result);
		
		$row['url'] = $this->_url($row, $cdr);
		
		$row['src_t'] = $this->_number($row['src'], $cdr);
		$row['dst_t'] = $this->_number($row['dst'], $cdr);
		
		$row['cost'] = $this->_cost($row);
		
		$row['status_t'] = $this->disposition[$row['disposition']];
				
		$row = $this->_view_t($row);

		return $row;
	}
	
	function _cost($row) {
		if ($row['cost'] > 0) {
			return $row['cost'];
		}
		
		require_once(dirname(__FILE__) . '/Charge.class.php');
		$aa = new Charge();
		
		return $aa->_cost($row['channel'], $row['dst'], $row['billsec'], $row['calldate']);
	}
	
	function delete($controller, $request) {
		$this->pretreat($controller);

		$db = newdb('cdr');
		while (list($k, $id) = each($request)) {
			if (substr($k, 0, 2) <> "c_") {
				continue;
			}

			$fmt = "DELETE FROM %s WHERE uniqueid = '%s'";
			$sql = sprintf($fmt, $this->tbl, $id);
			// debug($sql);
			$db->query($sql);
		}

		$controller->assign('baseLink', $this->doGet($request, 'findPost'));

		$this->findPost($controller, $request);
		return true;
	}

	function send($controller, $request) {
		$this->pretreat($controller);
		
		$uniqueid = $request['uniqueid'];
		
		$db = newdb('cdr');
		$fmt = "SELECT * FROM %s WHERE uniqueid = '%s'";
		$sql = sprintf($fmt, $this->tbl, $uniqueid);
		$result = $db->query($sql);
		$row = $db->fetch_array($result); 
		
		$controller->assign_by_ref('result', $row);
		
		$controller->display($request, 'tel/cdr/send');
		return true;
	}

	/**
	 *
	 *
	 * @param Controller $controller
	 * @param array $request
	 */
	function sendPost($controller, $request) {
		$this->pretreat($controller);
		
		$uniqueid = $request['uniqueid'];
		
		$db = newdb('cdr');
		$fmt = "SELECT * FROM %s WHERE uniqueid = '%s'";
		$sql = sprintf($fmt, $this->tbl, $uniqueid);
		$result = $db->query($sql);
		$row = $db->fetch_array($result); 

		$exten = $request['exten'];
		$number = $request['number'];
		$email = $request['email'];

		$content = addslashes($request['content']);
		
		$from = "sunxy@tecmold.com";
		$subject = sprintf("[电话]从%s至%s", $exten, $number);		

		require_once('Mail.php');
		require_once('Mail/mime.php');
		
		$headers['From'] = 'sunxy@tecmold.com';
		$headers['Subject'] = "=?UTF-8?B?".base64_encode($subject)."?=";
		
		$file = $this->_url($row, "cdr");		
		
		$mime = new Mail_mime;
		
		$html = sprintf("<html><body>%s<br/><br/>%s<br/><br/>%s<br/><br/>%s<br/></body></html>", $subject, $content, $file, $row['calldate']);
		$mime->setHTMLBody($html);
		
		$mp3 = "/tmp/a.mp3";
		$s = file_get_contents($file);
		file_put_contents($mp3, $s);
		$mime->addAttachment($mp3);
		
		$param["html_charset"] ='utf-8';
		$body = $mime->get($param);
		$headers = $mime->headers($headers);		
		
		$message = & Mail::supplier('mail');
		$message->send($email, $headers, $body);

		return true;
	}

	/**
	 *
	 *
	 * @param Controller $controller
	 * @param array $request
	 */
	function group($controller, $request) {
		$this->pretreat_group($controller);

		$controller->assign('t1', strtotime('-7 day'));
		
		$num = array(
			'1' => '按次数',
			'2' => '按费用',
		);
		$controller->assign('num_option', $num);

		$controller->display($request, 'tel/cdr/group');
		return true;
	}

	function groupby($controller, $px) {
		$px = $this->groupby_aa($px, $this->groupby, 'src');

		$controller->assign('groupby', $this->groupby[$px]);

		return $this->groupby_dd($px);
	}

	function groupby_dd($px) {
		if ($px == '1') {
			return "DATE_FORMAT(calldate, '%Y-%m-%d %H')";
		}

		if ($px == '2') {
			return "DATE_FORMAT(calldate, '%Y-%m-%d')";
		}

		if ($px == '3') {
			return "DATE_FORMAT(calldate, '%Y-%m')";
		}

		return $px;
	}

	function groupPost($controller, $request) {
		$this->pretreat($controller);
			
		$this->groupPara($controller, $request);
		
		$num_s = $request['num_s'];
		if (!empty($num_s) && $num_s == '2') {
			$fmt = "SELECT %s AS a, SUM(cost) AS c FROM %s a ";
		} else {
			$fmt = "SELECT %s AS a, COUNT(cost) AS c FROM %s a ";
		}
		$sql = sprintf($fmt, $this->s, $this->tbl);
		
		$where = $this->_where($request);
		
		$request['sql'] = sprintf("%s %s GROUP BY %s ORDER BY %s %s", $sql, $where, $this->s, $this->orderby, $this->desc);

		$cdr = $request['cdr_s'];
		if (empty($cdr) || $cdr == '-') {
			$cdr = 'cdr';
		}
		
		$this->dsn_r = $cdr;
		$this->_group($controller, $request);

		// $controller->display($request, 'list');
		return true;
	}

	function _name($row, $px) {
		if (empty($px) || $px == '-' || $px == 'src' || $px == 'dst') {
			if (preg_match("/^(2|6|8)\d{3}$/i", $row['a'])) {
				$emp = $this->_exten($row['a']);
				if (is_array($emp)) {
					$row['a'] = $emp['s'];
				}
			}
		}

		return $row;
	}

	/**
	 *
	 *
	 * @param Controller $controller
	 * @param array $request
	 */
	function wav($controller, $request) {
		$uniqueid = $request['uniqueid'];

		$cdr = $request['cdr_s'];
		if (empty($cdr) || $cdr == '-') {
			$cdr = 'cdr';
		}
		
		$row = $this->_view($uniqueid, $cdr);
		$row['url'] = $this->_url($row, $cdr);

		$controller->assign_by_ref('result', $row);

		$controller->display($request, 'tel/cdr/wav');
	}

	function _url($row, $cdr) {
		
		$ns = "http://ns11.tecmold.net/";
		$sj = "http://sj11.tecmold.net/";
		$qx = "http://qx11.tecmold.net/";
		
		$fy = "http://fy.tecmold.net/";
		$dh = "http://dh.tecmold.net/";

		if ($cdr == 'ns_sip' || $cdr == 'cdr') {
			if (preg_match("/^6\d{3}$/i", $row['src']) || preg_match("/^6\d{3}$/i", $row['dst'])) {
				return $ns . $row['filename'];
			}

			if (preg_match("/^8\d{3}$/i", $row['src']) || preg_match("/^8\d{3}$/i", $row['dst'])) {
				return $sj . $row['filename'];
			}
		}

		if ($cdr == 'sj_sip') {
			if (preg_match("/^8\d{3}$/i", $row['src']) || preg_match("/^8\d{3}$/i", $row['dst'])) {
				return $sj . $row['filename'];
			}

			if (preg_match("/^6\d{3}$/i", $row['src']) || preg_match("/^6\d{3}$/i", $row['dst'])) {
				return $ns . $row['filename'];
			}
		}
		
		if ($cdr == 'qx_sip') {
			if (preg_match("/^2\d{3}$/i", $row['src']) || preg_match("/^2\d{3}$/i", $row['dst'])) {
				return $qx . $row['filename'];
			}
		}

		if ($cdr == 'ns_sip') {
			return $ns . $row['filename'];
		}

		if ($cdr == 'sj_sip') {
			return $sj . $row['filename'];
		}
		
		if ($cdr == 'qx_sip') {
			return $qx . $row['filename'];
		}
		
		if ($cdr == 'dh_sip') {
			return $dh . $row['filename'];
		}
		
		if ($cdr == 'fy_sip') {
			return $fy . $row['filename'];
		}
	}
	
	function dh($controller, $request) {
		$src = "/var/spool/asterisk/monitor/";
		$dst = "/home/ftp/tecmold.com/cdr/";
	
		$this->_wav2mp3($src, $dst);
	}
	
	function ns($controller, $request) {
		$src = "/var/spool/asterisk/monitor/";
		$dst = "/home/ftp/tecmold.com/cdr/";
		
		$this->_wav2mp3($src, $dst);
	}
	
	function qx($controller, $request) {
		$src = "/var/spool/asterisk/monitor/";
		$dst = "/home/ftp/tecmold.com/cdr/";
	
		$this->_wav2mp3($src, $dst);
	}
	
	function sj($controller, $request) {
		$src = "/var/spool/asterisk/monitor";
		$dst = "/home/ftp/tecmold.com/cdr/";
		
		$this->_wav2mp3($src, $dst);
	}
	
	function fy($controller, $request) {
		$src = "/usr/local/asterisk/var/spool/asterisk/monitor/";
		$dst = "/home/ftp/tecmold.com/cdr/";
	
		$this->_wav2mp3($src, $dst);
	}

	function _wav2mp3($dir, $dst) {
		$dh = opendir($dir);
		while (($file = readdir($dh)) !== false) {
			if ($file == '.' || $file == '..') {
				continue;
			}
			
			$fmt = "%s/%s";
			$s = sprintf($fmt, $dir, $file);
			
			if (is_dir($s)) {
				echo $s . "\n";
				$this->_wav2mp3($s, $dst);
			} else {

				$filename = $dir . "/" . $file;
	
				$mp3 = $this->_mp3($dst, $file);
				if (empty($mp3) || file_exists($mp3)) {
					echo "E";
					continue;
				}
	
				$fmt = "/usr/local/lame/bin/lame %s %s\n";
				$cmd = sprintf($fmt, $filename, $mp3);
				// echo $cmd;
				shell_exec($cmd);
	
				$fmt = "/bin/rm %s\n";
				$cmd = sprintf($fmt, $filename);
				// echo $cmd;
				shell_exec($cmd);
				// echo $file . "\n";
				echo ".";
			}
		}

		closedir($dh);

		return true;
	}
	
	function _mp3($dst, $file) {
		if (preg_match("/([a-z]+)-(\d+)-(\d+)-(\d+)-(\d+)-([\d|.]+)(.wav)/i", $file, $m)) {
			return $this->_x($dst, $m);
		} else if (preg_match("/([A-Z]+)(\d+)-(\d+)-(\d+)-([\d|.]+)(.wav)/i", $file, $m)) {
			return $this->_out($dst, $m);
		} else if (preg_match("/(\d+)-(\d+)-([\d|.]+)(.wav)/i", $file, $m)) {
			return $this->_in($dst, $m);
		} 
	
		return '';
	}
	
	function _x($dst, $m) {
		$uniqueid = $m[6];
		$db = newdb('cdr');
		$fmt = "SELECT * FROM %s WHERE uniqueid = '%s'";
		$sql = sprintf($fmt, $this->tbl, $uniqueid);
		$result = $db->query($sql);
		if ($db->num_rows($result) == 0) {
			return '';
		}
		
		$dd = date('Y/m/d', strtotime($m[4]));
		$fmt = "%s/%s/";
		$dir = sprintf($fmt, $dd, $m[3]);
		$bb = $dst . $dir;
		if (!file_exists($bb)) {
			umask();
			mkdir($bb, 0777, true);
		}
		
		$mp3 = $uniqueid . ".mp3";
		
		$fmt = "UPDATE %s SET filename = '%s%s' WHERE uniqueid = '%s'";
		$sql = sprintf($fmt, $this->tbl, $dir, $mp3, $uniqueid);
		// debug($sql);
		$db->query($sql);
		
		return $bb . $mp3;
	}

	function _out($dst, $m) {
		$uniqueid = $m[5];
		$db = newdb('cdr');
		$fmt = "SELECT * FROM %s WHERE uniqueid = '%s'";
		$sql = sprintf($fmt, $this->tbl, $uniqueid);
		$result = $db->query($sql);
		if ($db->num_rows($result) == 0) {
			return '';
		}
		
		$dd = date('Y/m/d', strtotime($m[3]));
		$fmt = "%s/%s/%s/";
		$dir = sprintf($fmt, $m[1], $dd, $m[2]);
		$bb = $dst . $dir;
		if (!file_exists($bb)) {
			umask();
			mkdir($bb, 0777, true);
		}

		$mp3 = $uniqueid . ".mp3";

		$fmt = "UPDATE %s SET filename = '%s%s' WHERE uniqueid = '%s'";
		$sql = sprintf($fmt, $this->tbl, $dir, $mp3, $uniqueid);
		// debug($sql);
		$db->query($sql);

		return $bb . $mp3;
	}

	function _in($dst, $m) {
		$uniqueid = $m[3];
		$db = newdb('cdr');
		$fmt = "SELECT * FROM %s WHERE uniqueid = '%s'";
		$sql = sprintf($fmt, $this->tbl, $uniqueid);
		$result = $db->query($sql);
		if ($db->num_rows($result) == 0) {
			return '';
		}

		$row = $db->fetch_array($result);

		$dd = date('Y/m/d', strtotime($m[1]));
		$fmt = "%s/%s/%s/";
		$dir = sprintf($fmt, 'IN', $dd, $row['dst']);
		$bb = $dst . $dir;
		if (!file_exists($bb)) {
			umask();
			mkdir($bb, 0777, true);
		}

		$mp3 = $uniqueid . ".mp3";

		$fmt = "UPDATE %s SET filename = '%s%s' WHERE uniqueid = '%s'";
		$sql = sprintf($fmt, $this->tbl, $dir, $mp3, $uniqueid);
		// debug($sql);
		$db->query($sql);

		return $bb . $mp3;
	}
	
	function aa($controller, $request) {		
		$db = newdb('cdr');
		$fmt = "SELECT * FROM %s WHERE filename is NULL ORDER BY calldate DESC";
		$sql = sprintf($fmt, $this->tbl);
		echo $sql . "\n";
		$result = $db->query($sql);
		while ($row = $db->fetch_array($result)) {
			$ss = "%s/%s/%s/%s.mp3";
			$bb = '';
			$exten = '';
			if (preg_match("/^(6|8)\d{3}$/", $row['src'])) {
				$bb = 'OUT';
				$exten = $row['src'];
			} else if (preg_match("/^(6|8)\d{3}$/", $row['dst'])) {
				$bb = 'IN';
				$exten = $row['dst'];
			} else {
				continue;
			}
			
			$dd = date('Y/m/d', strtotime($row['calldate']));			
			$filename = sprintf($ss, $bb, $dd, $exten, $row['uniqueid']);
			
			$mp3 = $this->dst . $filename;
			// echo $mp3 . "\n";
			if (!file_exists($mp3)) {
				echo "E";
				continue;
			}
			
			$fmt = "UPDATE %s SET filename = '%s' WHERE uniqueid = '%s'";
			$sql = sprintf($fmt, $this->tbl, $filename, $row['uniqueid']);
			echo $sql . "\n";
			$db->query($sql);
		}

		logdate($controller, $request);
		return true;
	}
	
	function bb($controller, $request) {
		
		require_once(dirname(__FILE__) . '/Charge.class.php');
		$aa = new Charge();
		
		$dd = date('Y-m-d', strtotime('-3 day'));
		
		$db = newdb('cdr');
		$fmt = "SELECT * FROM %s WHERE cost is NULL ORDER BY calldate DESC";
		$fmt = "SELECT * FROM %s WHERE calldate > '%s' AND cost is NULL ORDER BY calldate DESC";
		// $fmt = "SELECT * FROM %s WHERE calldate > '%s' AND cost is NULL ORDER BY calldate DESC LIMIT 5";
		$sql = sprintf($fmt, $this->tbl, $dd);
		echo $sql . "\n";
		$result = $db->query($sql);
		while ($row = $db->fetch_array($result)) {
			$cost = $aa->_cost($row['channel'], $row['dst'], $row['billsec'], $row['calldate']);
				
			$fmt = "UPDATE %s SET cost = '%s' WHERE id = '%s'";
			$sql = sprintf($fmt, $this->tbl, $cost, $row['id']);
			echo $sql . "\n";
			$db->query($sql);
		}
	
		logdate($controller, $request);
		return true;
	}

	function z($controller, $request) {
		for ($i = 1; $i < 120; $i++) {
			$this->result = array();
			$t = date('Y-m-d', strtotime("-$i day"));
			$this->tj($t);

			reset($this->result);
			ksort($this->result);
			$ret = $t;
			while (list($k, $v) = each($this->result)) {
				$ret .= ", $k = " . $v;
			}
			$ret .= "\n\n";

			echo $ret;
		}

		logdate($controller, $request);
		return true;
	}

	function tj($t1) {
		// $t1 = date('Y-m-d', strtotime('-1 day'));
		$t2 = date('Y-m-d');

		$dd = "DATE_FORMAT(calldate, '%Y-%m-%d')";

		$db = newdb('cdr');
		$fmt = "SELECT * FROM %s WHERE disposition = '%s' and %s = '%s'";
		$sql = sprintf($fmt, $this->tbl, 'ANSWERED', $dd, $t1);
		// debug($sql);
		$result = $db->query($sql);
		while ($row = $db->fetch_array($result)) {
			// print_r($row);
			if (preg_match("/^8\d{3}$/i", $row['dst'])) {
				$this->result['ns'] += $row['billsec'];
			}

			if (preg_match("/^6\d{3}$/i", $row['src']) && preg_match("/^6\d{3}$/i", $row['dst'])) {
				$this->result['aa'] += $row['billsec'];
			}

			if (preg_match("/^8\d{3}$/i", $row['src'])) {
				$this->result['sj'] += $row['billsec'];
			}

			if (preg_match("/^917909\d{9,}$/i", $row['dst'])) {
				$this->result['17909'] += $row['billsec'];
			}

			if (preg_match("/^17\d{9,}$/i", $row['dst'])) {
				$this->result['17'] += $row['billsec'];
			}

			if (preg_match("/^9\d{8}$/i", $row['dst'])) {
				$this->result['sz8'] += $row['billsec'];
			}

			if (preg_match("/^9\d{11}$/i", $row['dst'])) {
				$this->result['sz11'] += $row['billsec'];
			}

			$this->result['t'] += $row['billsec'];
		}

		$this->result['jr'] = $this->result['ns'] + $this->result['sj'];

		while (list($k, $v) = each($this->result)) {
			$this->result[$k] = $this->minute($v);
		}

		// print_r($this->result);
	}

	function minute($time) {
		$minute = floor($time / 60);
		$second = $time % 60;

		if ($second > 0) {
			return $minute + 1;
		}

		return $minute;
	}

	function printtime($time) {
		$hour = floor($time / 3600);
		$minute = floor(($time % 3600) / 60);
		$second = $time % 60;

		return sprintf("%02d:%02d:%02d", $hour, $minute, $second);
	}

	function tj_ns($controller, $request) {
		$bb = $this->_list_email('314');
		// print_r($bb);
		if (!$bb || count($bb) == 0) {
			return false;
		}
		
		$request['tpl'] = "html4";

		$from = $this->email;
		$subject = "南山电话";
		
		$this->dsn_r = "cdr";

		$list = $this->_tj();
		if (!$list || count($list) == 0) {
			return false;
		}
		
		$controller->assign_by_ref('list', $list);
		
		$this->num_s = "2";

		$ns = array(
			'南山内部电话'=>array('w' => "src REGEXP '^6[0-9]{3}$' AND dst REGEXP '^6[0-9]{3}$'"),
			'南山拔打沙井'=>array('w' => "src REGEXP '^6[0-9]{3}$' AND dst REGEXP '^8[0-9]{3}$'"),
			'南山拔打市话'=>array('w' => "src REGEXP '^6[0-9]{3}$' AND dst REGEXP '^9[0-9]{7,11}$'"),
			'南山拔打长途'=>array('w' => "src REGEXP '^6[0-9]{3}$' AND dst REGEXP '^9179090[0-9]{10,}$'"),
			'南山拔打网络'=>array('w' => "src REGEXP '^6[0-9]{3}$' AND dst REGEXP '^1(7|9)[0-9]{9,}$'"),
			'南山拔打电话'=>array('w' => "src REGEXP '^6[0-9]{3}$'")
		);
		
		$this->_tj_bb($controller, $ns);
		
		$body = $controller->sunxy($request, 'tel/cdr/tj_list');
		
		$this->_tj_mail($from, $bb, $subject, $body);
		
		logdate($controller, $request);
		return true;
	}
	
	function tj_qx($controller, $request) {
		$bb = $this->_list_email('314');
		// print_r($bb);
		if (!$bb || count($bb) == 0) {
			return false;
		}
	
		$request['tpl'] = "html4";
	
		$from = $this->email;
		$subject = "清溪电话";
	
		$this->dsn_r = "cdr";
	
		$list = $this->_tj();
		if (!$list || count($list) == 0) {
			return false;
		}
	
		$controller->assign_by_ref('list', $list);
	
		$this->num_s = "2";
	
		$ns = array(
			'清溪内部电话'=>array('w' => "src REGEXP '^2[0-9]{3}$' AND dst REGEXP '^6[0-9]{3}$'"),
			'清溪拔打沙井'=>array('w' => "src REGEXP '^2[0-9]{3}$' AND dst REGEXP '^8[0-9]{3}$'"),
			'清溪拔打市话'=>array('w' => "src REGEXP '^2[0-9]{3}$' AND dst REGEXP '^9[0-9]{7,11}$'"),
			'清溪拔打长途'=>array('w' => "src REGEXP '^2[0-9]{3}$' AND dst REGEXP '^9179090[0-9]{10,}$'"),
			'清溪拔打网络'=>array('w' => "src REGEXP '^2[0-9]{3}$' AND dst REGEXP '^1(7|9)[0-9]{9,}$'"),
			'清溪拔打电话'=>array('w' => "src REGEXP '^2[0-9]{3}$'")
		);
	
		$this->_tj_bb($controller, $ns);
	
		$body = $controller->sunxy($request, 'tel/cdr/tj_list');
	
		$this->_tj_mail($from, $bb, $subject, $body);
	
		logdate($controller, $request);
		return true;
	}

	function tj_sj($controller, $request) {
		$bb = $this->_list_email('315');
		// print_r($bb);
		if (!$bb || count($bb) == 0) {
			return false;
		}

		$request['tpl'] = "html4";

		$subject = "沙井电话";
		$from = $this->email;
		
		$this->dsn_r = "cdr";
		
		$list = $this->_tj();
		if (!$list || count($list) == 0) {
			return false;
		}
		
		$controller->assign_by_ref('list', $list);
				
		$this->num_s = "2";

		$sj = array(
			'沙井内部电话'=>array('w' => "src REGEXP '^8[0-9]{3}$' AND dst REGEXP '^8[0-9]{3}$'"),
			'沙井拔打南山'=>array('w' => "src REGEXP '^8[0-9]{3}$' AND dst REGEXP '^6[0-9]{3}$'"),
			'沙井拔打市话'=>array('w' => "src REGEXP '^8[0-9]{3}$' AND dst REGEXP '^9[0-9]{7,11}$'"),
			'沙井拔打长途'=>array('w' => "src REGEXP '^8[0-9]{3}$' AND dst REGEXP '^9179090[0-9]{10,}$'"),
			'沙井拔打网络'=>array('w' => "src REGEXP '^8[0-9]{3}$' AND dst REGEXP '^1(7|9)[0-9]{9,}$'"),
			'沙井拔打电话'=>array('w' => "src REGEXP '^8[0-9]{3}$'")
			);

		$this->_tj_bb($controller, $aa);
		
		$body = $controller->sunxy($request, 'tel/cdr/tj_list');
		
		$this->_tj_mail($from, $bb, $subject, $body);
		
		logdate($controller, $request);
		return true;
	}

	function _tj() {
		$df = "DATE_FORMAT(DATE_ADD(now(), INTERVAL -1 DAY), '%Y-%m-%d')";
		
		$db = newdb($this->dsn_r);
		$fmt = "SELECT * FROM %s WHERE billsec > 300 AND calldate > %s ORDER BY id DESC";		
		$sql = sprintf($fmt, $this->tbl, $df);
		echo $sql . "\n";
		$result = $db->query($sql);
		$data = array();
		while ($row = $db->fetch_array($result)) {
			$row['src_o'] = $this->_number($row['src']);
			$row['dst_o'] = $this->_number($row['dst']);

			$row['billsec_t'] = $this->printtime($row['billsec']);
			
			$row['disposition'] = $this->disposition[$row['disposition']];
		
			$data[] = $row;
		}
		
		return $data;
	}
	
	function _tj2($v) {
		$this->result_s = 10;
		$this->groupby_s = 'src';
		$s = $this->groupby_s;
		
		$df = "DATE_FORMAT(calldate, '%Y-%m-%d')";
		$dd = date("Y-m-d", strtotime("-1 day"));

		$fmt = "SELECT %s AS a, SUM(billsec) AS c FROM %s a WHERE disposition = 'ANSWERED' AND %s AND %s = '%s' GROUP BY %s ORDER BY c DESC";
		$sql = sprintf($fmt, $s, $this->tbl, $v['w'], $df, $dd, $s);
		echo $sql . "\n";

		return $this->_group_tj2($sql);
	}

	function t($controller, $request) {
		$s = "88218059";

		$a = $this->src($s);

		debug(print_r($a, true));

		logdate($controller, $request);
		return true;
	}

	function d($controller, $request) {

		$ns = array('1'=>array('title'=>'南山内部电话', 'where' => "src REGEXP '^6[0-9]{3}$' AND dst REGEXP '^6[0-9]{3}$'"),
'2'=>array('title'=>'南山拔打沙井', 'where' => "src REGEXP '^6[0-9]{3}$' AND dst REGEXP '^8[0-9]{3}$'"),
'3'=>array('title'=>'南山拔打市话', 'where' => "src REGEXP '^6[0-9]{3}$' AND dst REGEXP '^9[0-9]{7,11}$'"),
'4'=>array('title'=>'南山拔打长途', 'where' => "src REGEXP '^6[0-9]{3}$' AND dst REGEXP '^9179090[0-9]{10,}$'"),
'5'=>array('title'=>'南山拔打网络', 'where' => "src REGEXP '^6[0-9]{3}$' AND dst REGEXP '^17[0-9]{9,}$'"),
'6'=>array('title'=>'南山拔打电话', 'where' => "src REGEXP '^6[0-9]{3}$'"));
		
		$sj = array(
'11'=>array('title'=>'沙井内部电话', 'where' => "src REGEXP '^8[0-9]{3}$' AND dst REGEXP '^8[0-9]{3}$'"),
'12'=>array('title'=>'沙井拔打南山', 'where' => "src REGEXP '^8[0-9]{3}$' AND dst REGEXP '^6[0-9]{3}$'"),
'13'=>array('title'=>'沙井拔打市话', 'where' => "src REGEXP '^8[0-9]{3}$' AND dst REGEXP '^9[0-9]{7,11}$'"),
'14'=>array('title'=>'沙井拔打长途', 'where' => "src REGEXP '^8[0-9]{3}$' AND dst REGEXP '^9179090[0-9]{10,}$'"),
'15'=>array('title'=>'沙井拔打网络', 'where' => "src REGEXP '^8[0-9]{3}$' AND dst REGEXP '^17[0-9]{9,}$'"),
'16'=>array('title'=>'沙井拔打电话', 'where' => "src REGEXP '^8[0-9]{3}$'"));

		$db = newdb('sj_sip');

		$df = "DATE_FORMAT(calldate, '%Y-%m-%d')";

		$s = "2010-05-01";
		// $s = "2010-08-01";
		$t1 = strtotime($s);
		while ($t1 < time()) {
			$dd = date("Y-m-d", $t1);
			// echo $dd;

			reset($sj);
			while (list($k, $v) = each($sj)) {
				$fmt = "SELECT SUM(billsec) AS c FROM %s WHERE disposition = 'ANSWERED' AND %s AND %s = '%s'";
				$sql = sprintf($fmt, $this->tbl, $v['where'], $df, $dd);
				echo $sql . "\n";
				$result = $db->query($sql);
				$row = $db->fetch_array($result);

				echo $row['c'] . "\n";

				$this->_tj_dd($dd, 'asteriskcdrdb.cdr', $k, $row['c']);
			}

			$t1 = strtotime("1 day", $t1);
		}

		logdate($controller, $request);
		return true;
	}


	function _tj_dd($dd, $tbl, $type, $num) {

		$db = newdb($this->dsn);
		$fmt = "INSERT INTO %s(dd, tbl, type, num, createtime) VALUES('%s', '%s', '%s', '%s', now())";
		$sql = sprintf($fmt, 'fy.tj_dd', $dd, $tbl, $type, $num);
		$db->query($sql);
	}

	function _num_t($t) {
		return $t;
		// return $this->printtime($t);
	}
	
	function t_send($controller, $request) {		
		require_once('Mail.php');
		require_once('Mail/mime.php');
		
		$to = 'sunxy@tecmold.com'; //收信人
		$headers['From'] = 'sunxy@tecmold.com'; //发信人
		$subject = '测试中文';//标题
		$headers['Subject'] = "=?UTF-8?B?".base64_encode($subject)."?="; //标题乱码
		
		$mime = new Mail_mime;
		
		$html ='<html><body>测试中文邮件</body></html>';
		$mime->setHTMLBody($html);
		
		//发送附件
		$file = '/home/sunxy/test.png'; //附件地址,该文件夹需要读权限
		$mime->addAttachment($file, 'image/png');
		$mime->addAttachment("/home/sunxy/t2.jpg", 'image/jpeg');
		$mime->addHTMLImage("/home/sunxy/t2.jpg");
		
		$param["html_charset"] ='utf-8'; //内容乱码
		$body = $mime->get($param);
		$headers = $mime->headers($headers);		
		
		$message = & Mail::supplier('mail', array());
		echo $message->send($to, $headers, $body);		
				
		logdate($controller, $request);
		return true;
	}
}
?>
